﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.IO;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;

namespace TotalSport
{
    public partial class UploadXslSheet : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            BAL.conn.connectionString = ConfigurationManager.ConnectionStrings["TotalSport"].ConnectionString;
        }
        protected void btnUpload_Click(object sender, EventArgs e)
        {
            if (FileUploadExcel.HasFile)
            {
                //string fileName = FileUploadExcel.PostedFile.FileName;
               // string fileName = FileUploadExcel.ResolveClientUrl(FileUploadExcel.PostedFile.FileName);
                string str = Path.GetFileName(FileUploadExcel.PostedFile.FileName); //DateTime.Now.Ticks.ToString() + ".xlsx";
                //string path=Server.MapPath("~/Excel/" + str);  
 
                if (str != "")
                {
                    FileUploadExcel.SaveAs(Server.MapPath("~/Excel/" + str));
                }
                //     OleDbConnection oconn = new OleDbConnection
                //(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                //Server.MapPath("Strucutreproduct.xls") + ";Extended Properties=Excel 8.0");
                //OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/Excel/" + str) + ";Extended Properties=Excel 8.0;HDR=YES;IMEX=1");
                OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("Excel/" + str) + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1'");
                //OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("Excel/" + filename) + ";Extended Properties=Excel 12.0;");
                string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("Excel/" + str) + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1'";
                string FilePath = Server.MapPath("~/Excel/" + str);
                conStr = String.Format(conStr, FilePath, 1);
                OleDbConnection connExcel = new OleDbConnection(conStr);
                OleDbCommand cmdExcel = new OleDbCommand();
                OleDbDataAdapter oda = new OleDbDataAdapter();
                cmdExcel.Connection = connExcel;
                connExcel.Open();
                DataTable dtExcelSchema;
                dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                //dSUploadInventory.Tables.Add(dtExcelSchema);
                connExcel.Close();

                //Read Data from First Sheet
                connExcel.Open();
                cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
                //oconn.Open();
                //DataTable dbSchema = oconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                //if (dbSchema == null || dbSchema.Rows.Count < 1)
                //{
                //    throw new Exception("Error: Could not determine the name of the first worksheet.");
                //}
                //string firstSheetName = dbSchema.Rows[0]["TABLE_NAME"].ToString();

                //// Now we have the table name; proceed as before:
                //OleDbCommand ocmd = new OleDbCommand("SELECT * FROM [" +  firstSheetName + "]", oconn);
   
                //OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
        
                //OledbConnection and 
                // connectionstring to connect to the Excel Sheet
                try
                {
                    //After connecting to the Excel sheet here we are selecting the data 
                    //using select statement from the Excel sheet
                    //OleDbCommand ocmd = new OleDbCommand("select * from [default$]", oconn);//"select * from [Sheet1$]", oconn);
                    //oconn.Open();  //Here [Sheet1$] is the name of the sheet 
                    //in the Excel file where the data is present
                    OleDbDataReader odr = cmdExcel.ExecuteReader();
                    BAL.Customers.TruncateProductDEtails();
                    lblmsg.Text = "";
                    string category = "";
                    string subcategory = "";
                    string particularName = "";
                    string brandname = "";
                    string itemcode = "";
                    string price = "";
                    string description = "";
                    string size = "";
                    string vat = "";
                    string smallImage = "";
                    string LargeImage = "";
                    string IDSub = "";

                    while (odr.Read())
                    {
                        //SubId = valid(odr, 0);
                        IDSub = valid(odr, 0);
                        category = valid(odr, 1);//Here we are calling the valid method
                        subcategory = valid(odr, 2);
                        particularName = valid(odr, 3);
                        brandname = valid(odr, 4);
                        itemcode = valid(odr, 5);
                        smallImage = valid(odr, 6);
                        price = valid(odr, 7);
                        description = valid(odr, 8);
                        size = valid(odr, 9);
                        vat = valid(odr,10);
                      
                        LargeImage = valid(odr, 11);
                        //IDSub = valid(odr, 11);
                        int SID = Convert.ToInt32(IDSub);

                        //Here using this method we are inserting the data into the database

                        BAL.Customers.insertdataintosql(category, subcategory, particularName
                            , brandname, itemcode, smallImage, price, description, size,
                            vat, LargeImage, SID);
                        BAL.Customers.InsertUpdateProductDetails(category, subcategory, particularName, brandname,
                            itemcode, smallImage, price, description, size, vat, LargeImage, SID);
                    }
                   
                }
                catch (DataException ee)
                {
                    lblmsg.Text = ee.Message;
                    lblmsg.ForeColor = System.Drawing.Color.Red;
                }
                finally
                {
                    lblmsg.Text = "Data Inserted Sucessfully";
                    lblmsg.ForeColor = System.Drawing.Color.Green;
                    oconn.Close();
                }
            }
            else {
                lblmsg.Text = "Please,Select Excel File";
                lblmsg.ForeColor = System.Drawing.Color.Green;
            }
           
        }
        //This valid method is mainly used to check where the null values are 
        //contained in the Excel Sheet and replacing them with zero
        protected string valid(OleDbDataReader myreader, int stval)//if any columns are 
        //found null then they are replaced by zero
        {
            object val = myreader[stval];
            if (val != DBNull.Value)
                return val.ToString();
            else
                return Convert.ToString(0);
        }

     

    }


}